<?php
  require_once("konf.php");
  $kask=$yhendus->prepare("SELECT hobused.hobune_ID, hobune_nimi FROM hobused
                           GROUP BY hobune_nimi
						   ORDER BY hobune_nimi 
						  ");
  $kask->bind_result($hobune_ID, $hobune_nimi);
  $kask->execute();
?>

<!doctype html>
<html>
  <head>
     <title>Statistika</title>
	 <link href="kujundus.css" rel="stylesheet" type="text/css" />
	 <meta charset="UTF-8">
  </head>
  <body>
   <div id="pealkiri">
  <h1>Kõik registreeritud hobused</h1>
  </div>
  <div id="main_container">
	<table>
	   <tr>
	     <th>Hobuse nimi</th>
	   </tr>	 
	<?php
         while($kask->fetch()){
		    $hobune_nimi=htmlspecialchars($hobune_nimi);
           echo "
            <tr>
             <td><a href='hobune.php?hobune_ID=$hobune_ID'>$hobune_nimi</a></td>
            </tr>  
           ";
      }
	  $kask->close();
    ?>
    </table>
	<h1>Hipodroomid</h1>
  <table>
     <tr>
	   <th>Hipodroomi nimi</th>
	   <th>Toimunud võistluseid</th>
	 </tr>
	<?php
        $kask=$yhendus->prepare("SELECT hipodroomi_nimi, COUNT(voistluse_nimi) FROM voistlused RIGHT JOIN hipodroomid 
		                         ON voistlused.hipodroom_ID=hipodroomid.hipodroom_ID GROUP BY hipodroomi_nimi ");
        $kask->bind_result($hipodroomi_nimi, $kogus);
        $kask->execute();
        while($kask->fetch()){
		  $hipodroomi_nimi=htmlspecialchars($hipodroomi_nimi);
          echo "
            <tr>
              <td><a href='hipodroom.php?hipodroomi_nimi=$hipodroomi_nimi'>$hipodroomi_nimi</a></td>
              <td>$kogus</td>
			</tr>
           ";
       }
	   $kask->close();
    ?>	
  </table>
  <h1>Firmad</h1>
  <table>
      <tr>
        <th>Firma nimi</th>
		<th>Hipodroome</th>
      </tr>
	  <?php
        $kask=$yhendus->prepare("SELECT firma_nimi, COUNT(hipodroomi_nimi) FROM hipodroomid RIGHT JOIN firmad ON hipodroomid.firma_ID=firmad.firma_ID 
								GROUP BY firma_nimi 
								ORDER BY firma_nimi 
								 ");
        $kask->bind_result($firma_nimi, $kogus);
        $kask->execute();
        while($kask->fetch()){
		  $firma_nimi=htmlspecialchars($firma_nimi);
          echo "
            <tr>
              <td><a href='firma.php?firma_nimi=$firma_nimi'>$firma_nimi</a></td>
			  <td>$kogus</td>
            </tr>
           ";
       }
	   $kask->close();
    ?>	
  </table>
  <h1>Järgmised treeningud</h1>
  <table>
     <tr>
	   <th>Algus</th>
	   <th>Lõpp</th>
	   <th>Treener</th>
	 </tr>
	 <?php
	  $kask=$yhendus->prepare("SELECT algus, l6pp, nimi FROM treeningud, hobuse_treenerid, kasutajad3 WHERE treeningud.hobuse_treenerid_ID=hobuse_treenerid.hobuse_treenerid_ID
	                            AND hobuse_treenerid.knimi=kasutajad3.knimi AND algus>NOW() ORDER BY algus
								 ");
      $kask->bind_result($algus, $l6pp, $nimi);
      $kask->execute();
	  while($kask->fetch()){
	  echo "
            <tr>
              <td>$algus</td>
			  <td>$l6pp</td>
			  <td>$nimi</td>
            </tr>
           ";
       }
	   $kask->close();
    ?>	
  </table>

	 </div>
	 
 </body>
</html>